Sort Property Example

This example demonstrates the Sort property by changing its value and creating a new Recordset. The SortOutput function is required for this procedure to run.

Sub SortX()

    Dim dbsNorthwind As Database
    Dim rstEmployees As Recordset
    Dim rstSortEmployees As Recordset

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set rstEmployees = _
        dbsNorthwind.OpenRecordset("Employees", _
        dbOpenDynaset)

    With rstEmployees
        SortOutput "Original Recordset:", rstEmployees
        .Sort = "LastName, FirstName"
        ' Print report showing Sort property and record order.
        SortOutput _
            "Recordset after changing Sort property:", _
            rstEmployees
        ' Open new Recordset from current one.
        Set rstSortEmployees = .OpenRecordset
        ' Print report showing Sort property and record order.
        SortOutput "New Recordset:", rstSortEmployees
        rstSortEmployees.Close
        .Close
    End With

    dbsNorthwind.Close

End Sub

Function SortOutput(strTemp As String, _
    rstTemp As Recordset)

    With rstTemp
        Debug.Print strTemp
        Debug.Print "  Sort = " & _
            IIf(.Sort <> "", .Sort, "[Empty]")
        .MoveFirst

        ' Enumerate Recordset.
        Do While Not .EOF
            Debug.Print "    " & !LastName & _
                ", " & !FirstName
            .MoveNext
        Loop

    End With

End Function

Note When you know the data you want to select, it's usually more efficient to create a Recordset with an SQL statement. This example shows how you can create just one Recordset and obtain the same results as in the preceding example.

Sub SortX2()

    Dim dbsNorthwind As Database
    Dim rstEmployees As Recordset

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    ' Open a Recordset from an SQL statement that specifies a 
    ' sort order.
    Set rstEmployees = _
        dbsNorthwind.OpenRecordset("SELECT * " & _
        "FROM Employees ORDER BY LastName, FirstName", _
        dbOpenDynaset)

    dbsNorthwind.Close

End Sub